"""
验证和修复数据库中的无效datetime字段
用于检查和修复零值日期时间问题
"""
import logging
from django.core.management.base import BaseCommand
from django.db import connection
from django.utils import timezone
from authentication.models import Permission, Role, User

logger = logging.getLogger(__name__)


class Command(BaseCommand):
    help = '验证和修复数据库中的无效datetime字段'

    def add_arguments(self, parser):
        parser.add_argument(
            '--fix',
            action='store_true',
            help='自动修复发现的无效日期时间值',
        )
        parser.add_argument(
            '--dry-run',
            action='store_true',
            help='只检查不修复（默认行为）',
        )

    def handle(self, *args, **options):
        self.stdout.write('开始验证数据库中的datetime字段...')
        
        fix_mode = options['fix']
        dry_run = options['dry_run']
        
        if fix_mode and dry_run:
            self.stdout.write(
                self.style.ERROR('--fix 和 --dry-run 不能同时使用')
            )
            return
        
        if not fix_mode and not dry_run:
            # 默认为dry-run模式
            dry_run = True
            self.stdout.write('使用检查模式（--dry-run），如需修复请添加 --fix 参数')
        
        issues_found = 0
        issues_fixed = 0
        
        # 检查权限表
        self.stdout.write('\n检查权限表 (auth_custom_permission)...')
        perm_issues, perm_fixed = self.check_permission_table(fix_mode)
        issues_found += perm_issues
        issues_fixed += perm_fixed
        
        # 检查角色表
        self.stdout.write('\n检查角色表 (auth_role)...')
        role_issues, role_fixed = self.check_role_table(fix_mode)
        issues_found += role_issues
        issues_fixed += role_fixed
        
        # 检查用户表
        self.stdout.write('\n检查用户表 (auth_user)...')
        user_issues, user_fixed = self.check_user_table(fix_mode)
        issues_found += user_issues
        issues_fixed += user_fixed
        
        # 总结
        self.stdout.write('\n' + '='*50)
        if issues_found == 0:
            self.stdout.write(
                self.style.SUCCESS('✅ 没有发现无效的datetime字段')
            )
        else:
            if fix_mode:
                self.stdout.write(
                    self.style.SUCCESS(
                        f'发现 {issues_found} 个问题，修复了 {issues_fixed} 个'
                    )
                )
            else:
                self.stdout.write(
                    self.style.WARNING(
                        f'发现 {issues_found} 个问题。运行命令时添加 --fix 参数来修复这些问题'
                    )
                )

    def check_permission_table(self, fix_mode):
        """检查权限表的datetime字段"""
        issues_found = 0
        issues_fixed = 0
        
        with connection.cursor() as cursor:
            # 查找无效的created_at值
            cursor.execute("""
                SELECT id, name, created_at 
                FROM auth_custom_permission 
                WHERE created_at = '0000-00-00 00:00:00.000000' 
                   OR created_at IS NULL 
                   OR created_at < '1900-01-01 00:00:00'
            """)
            
            invalid_records = cursor.fetchall()
            issues_found = len(invalid_records)
            
            if invalid_records:
                self.stdout.write(
                    f'  发现 {len(invalid_records)} 条权限记录有无效created_at值:'
                )
                
                for record in invalid_records:
                    self.stdout.write(f'    ID: {record[0]}, Name: {record[1]}, Created: {record[2]}')
                
                if fix_mode:
                    # 修复无效值
                    cursor.execute("""
                        UPDATE auth_custom_permission 
                        SET created_at = NOW() 
                        WHERE created_at = '0000-00-00 00:00:00.000000' 
                           OR created_at IS NULL 
                           OR created_at < '1900-01-01 00:00:00'
                    """)
                    issues_fixed = cursor.rowcount
                    self.stdout.write(
                        self.style.SUCCESS(f'  ✅ 修复了 {issues_fixed} 条记录')
                    )
            else:
                self.stdout.write('  ✅ 权限表datetime字段正常')
        
        return issues_found, issues_fixed

    def check_role_table(self, fix_mode):
        """检查角色表的datetime字段"""
        issues_found = 0
        issues_fixed = 0
        
        with connection.cursor() as cursor:
            # 查找无效的created_at和updated_at值
            cursor.execute("""
                SELECT id, name, created_at, updated_at 
                FROM auth_role 
                WHERE created_at = '0000-00-00 00:00:00.000000' 
                   OR created_at IS NULL 
                   OR created_at < '1900-01-01 00:00:00'
                   OR updated_at = '0000-00-00 00:00:00.000000' 
                   OR updated_at IS NULL 
                   OR updated_at < '1900-01-01 00:00:00'
            """)
            
            invalid_records = cursor.fetchall()
            issues_found = len(invalid_records)
            
            if invalid_records:
                self.stdout.write(
                    f'  发现 {len(invalid_records)} 条角色记录有无效datetime值:'
                )
                
                for record in invalid_records:
                    self.stdout.write(f'    ID: {record[0]}, Name: {record[1]}, Created: {record[2]}, Updated: {record[3]}')
                
                if fix_mode:
                    # 修复无效值
                    cursor.execute("""
                        UPDATE auth_role 
                        SET created_at = CASE 
                                WHEN created_at = '0000-00-00 00:00:00.000000' OR created_at IS NULL OR created_at < '1900-01-01 00:00:00' 
                                THEN NOW() 
                                ELSE created_at 
                            END,
                            updated_at = CASE 
                                WHEN updated_at = '0000-00-00 00:00:00.000000' OR updated_at IS NULL OR updated_at < '1900-01-01 00:00:00' 
                                THEN NOW() 
                                ELSE updated_at 
                            END
                        WHERE created_at = '0000-00-00 00:00:00.000000' 
                           OR created_at IS NULL 
                           OR created_at < '1900-01-01 00:00:00'
                           OR updated_at = '0000-00-00 00:00:00.000000' 
                           OR updated_at IS NULL 
                           OR updated_at < '1900-01-01 00:00:00'
                    """)
                    issues_fixed = cursor.rowcount
                    self.stdout.write(
                        self.style.SUCCESS(f'  ✅ 修复了 {issues_fixed} 条记录')
                    )
            else:
                self.stdout.write('  ✅ 角色表datetime字段正常')
        
        return issues_found, issues_fixed

    def check_user_table(self, fix_mode):
        """检查用户表的datetime字段"""
        issues_found = 0
        issues_fixed = 0
        
        with connection.cursor() as cursor:
            # 查找无效的date_joined值
            cursor.execute("""
                SELECT id, email, date_joined 
                FROM auth_user 
                WHERE date_joined = '0000-00-00 00:00:00.000000' 
                   OR date_joined IS NULL 
                   OR date_joined < '1900-01-01 00:00:00'
            """)
            
            invalid_records = cursor.fetchall()
            issues_found = len(invalid_records)
            
            if invalid_records:
                self.stdout.write(
                    f'  发现 {len(invalid_records)} 条用户记录有无效date_joined值:'
                )
                
                for record in invalid_records:
                    self.stdout.write(f'    ID: {record[0]}, Email: {record[1]}, Joined: {record[2]}')
                
                if fix_mode:
                    # 修复无效值
                    cursor.execute("""
                        UPDATE auth_user 
                        SET date_joined = NOW() 
                        WHERE date_joined = '0000-00-00 00:00:00.000000' 
                           OR date_joined IS NULL 
                           OR date_joined < '1900-01-01 00:00:00'
                    """)
                    issues_fixed = cursor.rowcount
                    self.stdout.write(
                        self.style.SUCCESS(f'  ✅ 修复了 {issues_fixed} 条记录')
                    )
            else:
                self.stdout.write('  ✅ 用户表datetime字段正常')
        
        return issues_found, issues_fixed